﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Configuration;
using System.IO;

namespace SQLiteStudy
{
    public partial class Main : Form
    {
        SQLiteConnection conn;
        
        public Main()
        {
            InitializeComponent();
        }


        private void GetConnection()
        {
            if (conn != null)
            {
                return;
            }

            string connString= ConfigurationManager.ConnectionStrings["SQLite Data Provider"].ConnectionString;
            conn = new SQLiteConnection(connString);
            if (string.IsNullOrEmpty( conn.DataSource)==false)
            {
                FileInfo file = new FileInfo(conn.DataSource);
                if (file.Exists == false)
                {
                    FileStream stream= file.Create();
                    stream.Close();
                }
            }

            conn.Open();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.GetConnection();
            if (conn != null)
            {
                this.textBox1.Text = string.Format("连接已经创建,并且其状态为:{0}", conn.State.ToString());
            }
            else
            {
                this.textBox1.Text = string.Format("连接没有创建成功.");
            }
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            conn.Clone();
            conn.Dispose();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string commString= "Insert into Users ([name]) values (?)";
            int execResult = 0;
            using (SQLiteCommand comm = new SQLiteCommand(commString, conn))
            {
                SQLiteParameter para = new SQLiteParameter();
                para.Value = DateTime.Now.ToString();
                comm.Parameters.Add(para);

                execResult= comm.ExecuteNonQuery();
            }

            if (execResult > 0)
            {
                this.textBox1.Text = "插入数据成功";
            }
        }

        private void Main_Load(object sender, EventArgs e)
        {
            this.GetConnection();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string commString = "Select count(*) from [Users]";
            int execResult = -0;
            using (SQLiteCommand comm = new SQLiteCommand(commString, conn))
            {
                execResult = Convert.ToInt32(comm.ExecuteScalar());
            }

            if (execResult > 0)
            {
                this.textBox1.Text = string.Format("当前数据库共有{0}条数据", execResult.ToString());
            }
            else 
            {
                this.textBox1.Text = "当前数据库内没有数据";
            }
        }
    }
}
